package me.landery.modules.sinco.repository;

import me.landery.modules.sinco.domain.Call;
import me.landery.modules.sinco.domain.Mode;
import me.landery.modules.system.domain.Role;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Set;

public interface CallRepository extends JpaRepository<Call, Long>, JpaSpecificationExecutor<Call> {
    /**
     * 根据 PID 查询
     * @param name pid
     * @return /
     */
    List<Call> findByContent(String name);

    /**
     * 根据用户ID查询
     * @param id 用户ID
     * @return /
     */
    @Query(value = "SELECT c.* FROM sinco_call c WHERE " +
            "c.uid = ?1",nativeQuery = true)
    Set<Call> findByUserId(Long id);

    @Query(value = "select DATE_FORMAT(c.`time`,'%H') hours,c.`success` as success,count(c.cid) as num  " +
            "from sinco_call c left join sinco_app a on c.aid = a.id " +
            "where to_days(c.`time`) = to_days(?2) and c.uid= ?1 and (?3 is null or a.appid = ?3) " +
            "group by hours,success order by hours,success",nativeQuery = true)
    List<Map<String,Object>> queryCurrentDayTotal(Long userId,Date date, String appId);

    @Query(value = "SELECT DATE_FORMAT(c.`time`,'%Y-%m-%d') as date_time,c.`success` as success,count(c.cid) as num " +
            "from  sinco_call c left join sinco_app a on c.aid = a.id " +
            "where (to_days(?2)-to_days(c.`time`))<=10 and c.uid = ?1 and (?3 is null or a.appid = ?3) " +
            "GROUP BY date_time,success " +
            "ORDER BY date_time,success",nativeQuery = true)
    List<Map<String, Object>> queryHis10DayTotal(Long id, Date date, String appId);

    @Query(value = "SELECT `success`,count(cid) as num " +
            "from sinco_call " +
            "where to_days(?2)=to_days(`time`) and uid =?1 " +
            "GROUP BY `success`",nativeQuery = true)
    List<Map<String,Object>> queryCurrentDayRecordGk(Long userId,Date date);

    @Query(value = "SELECT `success`,count(cid) as num " +
            "from sinco_call " +
            "where  uid =?1 " +
            "GROUP BY `success`",nativeQuery = true)
    List<Map<String, Object>> queryAllRecordGk(Long id);

    @Query(value = "select c.`type` as c_type,count(c.cid) as num " +
            "from sinco_call c left join sinco_app a on c.aid = a.id " +
            "where to_days(?2)=to_days(c.`time`) and c.uid =?1 and (?3 is null or a.appid = ?3) " +
            "GROUP BY c_type",nativeQuery = true)
    List<Map<String, Object>> getCurrentToDayCallTypePercent(Long id,Date date, String appId);

    @Query(value = "select c.`type` as c_type,count(c.cid) as num " +
            "from sinco_call c left join sinco_app a on c.aid = a.id " +
            "where c.uid =?1 and (?2 is null or a.appid = ?2) " +
            "GROUP BY c_type",nativeQuery = true)
    List<Map<String, Object>> getAllCallTypePercent(Long id, String appId);
}